package com.kinyx.framework.db.daos;

import static org.junit.Assert.assertNull;

import java.sql.SQLException;

import org.junit.Assert;
import org.junit.Test;

import com.kinyx.framework.db.configs.DBConfigsHelper;
import com.kinyx.framework.db.datasource.Datasource;
import com.kinyx.framework.db.sentence.res.Condition;
import com.kinyx.framework.db.sentence.res.KSELECT;
import com.kinyx.framework.db.sentence.res.KSQLBuilder;
import com.kinyx.framework.db.sentence.str.SELECT;
import com.kinyx.framework.db.sentence.str.SQL;
import com.kinyx.framework.db.sentence.str.SQLBuilder;
import com.kinyx.framework.enums.impl.ErrorCode1;
import com.kinyx.framework.enums.impl.ErrorCodeX;
import com.kinyx.framework.enums.impl.UsingStatus;
import com.kinyx.framework.exception.KRuntimeException;
import com.kinyx.framework.reflect.KFieldUtils;
import com.kinyx.framework.utils.KDateTimeUtils;
import com.kinyx.test.entities.impl.DemoEntityR;

@SuppressWarnings("deprecation")
public abstract class JDBCDao_Tester_queryValue extends SUPER {

	@Test
	public void testSuccess1() {
		this.logger.debug("queryValue using str.SQL");
		final String value = this.dao.queryValue(null, new SQL().x("SELECT ID FROM T_DEMO WHERE ID = ?", null), String.class);
		assertNull(value);
	}

	@Test
	public void testSuccess2() {
		this.logger.debug("getValue using str.SELECT");
		final SELECT sentence = SQLBuilder.select().x("SELECT ID FROM T_DEMO WHERE ID = ?", null);
		final String value = this.dao.getValue(null, sentence, String.class);
		assertNull(value);
	}

	@Test
	public void testSuccess3() {
		this.logger.debug("getValue using res.SELECT");
		final KSELECT sentence = KSQLBuilder.select(true)//
				.select(DemoEntityR.id)//
				.from(DemoEntityR.class)//
				.where(Condition.eq(DemoEntityR.id, null));
		final String value = this.dao.getValue(null, sentence, String.class);
		assertNull(value);
	}

	@Test
	public void testError0() {
		this.logger.debug("testError0 连接数据库失败（当地址有误时，实际就没与数据库进行交互；当用户名或密码有误时，实际已经与数据库交互，数据库拒绝连接。）");
		final Datasource datasource = DBConfigsHelper.getDBConfigs(this.dao.getKey()).getDatasource();
		final String password = datasource.getPassword();
		KFieldUtils.set(datasource, KFieldUtils.getField(datasource.getClass(), "password", false, true), "");
		Assert.assertThrows(Exception.class, () -> {
			try {
				this.dao.queryValue(null, new SQL().x("SELECT ID FROM T_DEMO WHERE ID = ?", null), String.class);
			} catch (final Exception e) {
				Assert.assertTrue(e instanceof KRuntimeException);
				Assert.assertEquals(((KRuntimeException) e).getErrorCode(), ErrorCode1.CODE_ERROR_101);
				throw e;
			}
		});
		KFieldUtils.set(datasource, KFieldUtils.getField(datasource.getClass(), "password", false, true), password);
	}

	@Test
	public void testError11() {
		this.logger.debug("testError11 空指针（尚未连接数据库）");
		final SQL sql = null;
		Assert.assertThrows(NullPointerException.class, () -> { this.dao.queryValue(null, sql, String.class); });
	}

	@Test
	public void testError2() {
		this.logger.debug("testError2 执行参数与SQL对应关系有误，实际参数多。（在prepareStatement时报错）");
		Assert.assertThrows(Exception.class, () -> {
			try {
				this.dao.queryValue(null, new SQL().x("SELECT ID FROM T_DEMO WHERE ID = ?", null).p(null), String.class);
			} catch (final Exception e) {
				Assert.assertTrue(e instanceof KRuntimeException);
				Assert.assertEquals(((KRuntimeException) e).getErrorCode(), ErrorCode1.CODE_ERROR_103);
				Assert.assertTrue(e.getCause() instanceof SQLException);
				switch (DBConfigsHelper.getDBConfigs(this.dao.getKey()).getDialect().name()) {
					case "MySQL":
						Assert.assertTrue(e.getCause().getMessage().startsWith("Parameter index out of range"));
						break;
					case "Oracle":
						Assert.assertTrue(e.getCause().getMessage().startsWith("无效的列索引"));
						break;
					default :
						throw new KRuntimeException(ErrorCodeX.NotImplemented);
				}
				throw e;
			}
		});
	}

	@Test
	public void testError3() {
		this.logger.debug("testError3 执行参数与SQL对应关系有误，实际参数少。（在executeQuery时报错，并未真正在数据库中执行。）");
		Assert.assertThrows(Exception.class, () -> {
			try {
				this.dao.queryValue(null, new SQL().s("SELECT ID FROM T_DEMO WHERE ID = ?"), String.class);
			} catch (final Exception e) {
				Assert.assertTrue(e instanceof KRuntimeException);
				Assert.assertEquals(((KRuntimeException) e).getErrorCode(), ErrorCode1.CODE_ERROR_107);
				Assert.assertTrue(e.getCause() instanceof SQLException);
				switch (DBConfigsHelper.getDBConfigs(this.dao.getKey()).getDialect().name()) {
					case "MySQL":
						Assert.assertTrue(e.getCause().getMessage().startsWith("No value specified for parameter"));
						break;
					case "Oracle":
						Assert.assertTrue(e.getCause().getMessage().startsWith("索引中丢失"));
						break;
					default :
						throw new KRuntimeException(ErrorCodeX.NotImplemented);
				}
				throw e;
			}
		});
	}

	@Test
	public void testError4() {
		this.logger.debug("testError4 语句有误（在executeQuery时报错，并未真正在数据库中执行。）");
		Assert.assertThrows(Exception.class, () -> {
			try {
				this.dao.queryValue(null, new SQL().s("SELECT"), String.class);
			} catch (final Exception e) {
				Assert.assertTrue(e instanceof KRuntimeException);
				Assert.assertEquals(((KRuntimeException) e).getErrorCode(), ErrorCode1.CODE_ERROR_107);
				Assert.assertTrue(e.getCause() instanceof SQLException);
				switch (DBConfigsHelper.getDBConfigs(this.dao.getKey()).getDialect().name()) {
					case "MySQL":
						Assert.assertTrue(e.getCause().getMessage().startsWith("You have an error in your SQL syntax"));
						break;
					case "Oracle":
						Assert.assertTrue(e.getCause().getMessage().startsWith("ORA-00936: 缺失表达式"));
						break;
					default :
						throw new KRuntimeException(ErrorCodeX.NotImplemented);
				}
				throw e;
			}
		});
	}

	@Test
	public void testError5() {
		this.logger.debug("testError5 表不存在（已经在数据库中执行）");
		Assert.assertThrows(Exception.class, () -> {
			try {
				this.dao.queryValue(null, new SQL().x("SELECT ID FROM T_NOT_EXIST WHERE ID = ?", null), String.class);
			} catch (final Exception e) {
				Assert.assertTrue(e instanceof KRuntimeException);
				Assert.assertEquals(((KRuntimeException) e).getErrorCode(), ErrorCode1.CODE_ERROR_107);
				Assert.assertTrue(e.getCause() instanceof SQLException);
				switch (DBConfigsHelper.getDBConfigs(this.dao.getKey()).getDialect().name()) {
					case "MySQL":
						Assert.assertTrue(e.getCause().getMessage().contains("T_NOT_EXIST' doesn't exist"));
						break;
					case "Oracle":
						Assert.assertTrue(e.getCause().getMessage().startsWith("ORA-00942: 表或视图不存在"));
						break;
					default :
						throw new KRuntimeException(ErrorCodeX.NotImplemented);
				}
				throw e;
			}
		});
	}

	@Test
	public void testError6() {
		this.logger.debug("testError6 列不存在（已经在数据库中执行）");
		Assert.assertThrows(Exception.class, () -> {
			try {
				this.dao.queryValue(null, new SQL().x("SELECT ID FROM T_DEMO WHERE NOT_EXIST = ?", null), String.class);
			} catch (final Exception e) {
				Assert.assertTrue(e instanceof KRuntimeException);
				Assert.assertEquals(((KRuntimeException) e).getErrorCode(), ErrorCode1.CODE_ERROR_107);
				Assert.assertTrue(e.getCause() instanceof SQLException);
				switch (DBConfigsHelper.getDBConfigs(this.dao.getKey()).getDialect().name()) {
					case "MySQL":
						Assert.assertTrue(e.getCause().getMessage().startsWith("Unknown column 'NOT_EXIST'"));
						break;
					case "Oracle":
						Assert.assertTrue(e.getCause().getMessage().startsWith("ORA-00904: \"NOT_EXIST\": 标识符无效"));
						break;
					default :
						throw new KRuntimeException(ErrorCodeX.NotImplemented);
				}
				throw e;
			}
		});
	}

	@Test
	public void testError7() {
		this.logger.debug("testError7 结果集数量过多（已经在数据库中执行）");
		final int cnt1 = this.dao.insert(null, new SQL().x("INSERT INTO T_DEMO (ID, USING_STATUS, INSERT_TIME, UPDATE_TIME) VALUES (?, ?, ?, ?)", //
				"1", //
				UsingStatus.USING, //
				KDateTimeUtils.now("yyyyMMddHHmmssSSS"), //
				KDateTimeUtils.now("yyyyMMddHHmmssSSS")//
		));
		Assert.assertTrue(cnt1 == 1);
		final int cnt2 = this.dao.insert(null, new SQL().x("INSERT INTO T_DEMO (ID, USING_STATUS, INSERT_TIME, UPDATE_TIME) VALUES (?, ?, ?, ?)", //
				"2", //
				UsingStatus.USING, //
				KDateTimeUtils.now("yyyyMMddHHmmssSSS"), //
				KDateTimeUtils.now("yyyyMMddHHmmssSSS")//
		));
		Assert.assertTrue(cnt2 == 1);

		Assert.assertThrows(Exception.class, () -> {
			try {
				this.dao.queryValue(null, new SQL().s("SELECT ID FROM T_DEMO"), String.class);
			} catch (final Exception e) {
				Assert.assertTrue(e instanceof KRuntimeException);
				Assert.assertEquals(((KRuntimeException) e).getErrorCode(), ErrorCode1.CODE_ERROR_802);
				final int cnt3 = this.dao.delete(null, new SQL().x("DELETE FROM T_DEMO WHERE ID = ?", "1"));
				Assert.assertTrue(cnt3 == 1);
				final int cnt4 = this.dao.delete(null, new SQL().x("DELETE FROM T_DEMO WHERE ID = ?", "2"));
				Assert.assertTrue(cnt4 == 1);
				throw e;
			}
		});
	}

	@Test
	public void testError8() {
		this.logger.debug("testError8 结果集列过多（已经在数据库中执行）");
		final int cnt1 = this.dao.insert(null, new SQL().x("INSERT INTO T_DEMO (ID, USING_STATUS, INSERT_TIME, UPDATE_TIME) VALUES (?, ?, ?, ?)", "1", "USING", KDateTimeUtils.now("yyyyMMddHHmmssSSS"), KDateTimeUtils.now("yyyyMMddHHmmssSSS")));
		Assert.assertTrue(cnt1 == 1);

		Assert.assertThrows(Exception.class, () -> {
			try {
				this.dao.queryValue(null, new SQL().s("SELECT ID, USING_STATUS FROM T_DEMO"), String.class);
			} catch (final Exception e) {
				Assert.assertTrue(e instanceof KRuntimeException);
				Assert.assertEquals(((KRuntimeException) e).getErrorCode(), ErrorCode1.CODE_ERROR_801);
				final int cnt3 = this.dao.delete(null, new SQL().x("DELETE FROM T_DEMO WHERE ID = ?", "1"));
				Assert.assertTrue(cnt3 == 1);
				throw e;
			}
		});
	}

}
